package com.homework.dao;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import com.homework.action.DeleteAction;
import com.homework.action.PictureAction;
import com.homework.doamin.Course;
import com.homework.doamin.HomeworkItem;
import com.sina.sae.storage.SaeStorage;
import com.sina.sae.util.SaeUserInfo;



public class CourseUtil {
	private static String driver="com.mysql.jdbc.Driver";
	private String url="jdbc:mysql://w.rdc.sae.sina.com.cn:3307/app_homework71";
	private String username=SaeUserInfo.getAccessKey();
	private String password=SaeUserInfo.getSecretKey();
	
//	private String url="jdbc:mysql://localhost:3306/homework";
//	private String username="root";
//	private String password="a472251631";
	
	private PreparedStatement pstm;
	private Connection conn=null;
	static{
		try {
			Class.forName(driver).newInstance();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public  Connection getConnection()throws Exception{
		try {
			return DriverManager.getConnection(url+"?user="+username+"&password="+password+"&useUnicode=true&characterEncoding=utf-8" );
		} catch (Exception e) {
			// TODO Auto-generated catch block
			return null;
		}
		
	}
	
	public void close(){
		if(pstm!=null){
			try {
				pstm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	
	public Course getCourseByAcronym(String acronym)throws Exception{
		conn=getConnection();
		Course course=null;
		ResultSet resultSet=null;
		try{
			course=new Course();
			pstm=conn.prepareStatement("select * from course_base where course_acronym=?");
			pstm.setString(1, acronym);
			resultSet=pstm.executeQuery();
			while(resultSet.next()){
				course.setCourseId((Integer)resultSet.getObject("course_id"));
				course.setCourseName((String)resultSet.getObject("course_name"));
				course.setCourseAcronym(resultSet.getString("course_acronym"));
			}
		}catch (Exception e){
			throw e;
		}finally{
			try {
				resultSet.close();
				pstm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw e;
			}
			close();
		}
		return course;
	}
	
	public List<HomeworkItem> getHomeworkItem(Course course, int currentPage) throws Exception{
		List<HomeworkItem> homeworks=new ArrayList<HomeworkItem>();
		conn=getConnection();
		ResultSet resultSet=null;
		int[] homeworkId=null;
		try{
			pstm=conn.prepareStatement("select * from homework where course_id=? order by date_time desc limit ?,5");
			pstm.setInt(1, course.getCourseId());
			pstm.setInt(2, (currentPage-1)*5);
			resultSet=pstm.executeQuery();
			while(resultSet.next()){
				HomeworkItem homeworkItem=new HomeworkItem();
				homeworkItem.setHomeworkId((Integer)resultSet.getObject("homework_id"));
				homeworkItem.setDatetime2(resultSet.getTimestamp("date_time").toString());
				homeworkItem.setPs((String)resultSet.getObject("ps"));
				homeworks.add(homeworkItem);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				resultSet.close();
				pstm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			close();
		}
		return homeworks;
	}
	
//	public List<String> getPictureAddr(HomeworkItem homeworkItem){
//		conn=getConnection();
//		ResultSet resultSet=null;
//		List<String> pictureAddr=new ArrayList<String>();
//		try{
//			pstm=conn.prepareStatement("select * from picture where homework_id=?");
//			pstm.setInt(1, homeworkItem.getHomeworkId());
//			resultSet=pstm.executeQuery();
//			while(resultSet.next()){
//				pictureAddr.add(resultSet.getString("addr"));
//			}
//		}catch(Exception e){
//			e.printStackTrace();
//		}finally{
//			try {
//				resultSet.close();
//				pstm.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//			close();
//		}
//		return pictureAddr;
//	}
	
	public List<String> getPictureAddr(String homeworkId, PictureAction pictureAction)throws Exception{
		SaeStorage saeSto=new SaeStorage();
		conn=getConnection();
		ResultSet resultSet=null;
		List<String> pictureAddr=new ArrayList<String>();
		try{
			pstm=conn.prepareStatement("select * from picture where homework_id=?");
			pstm.setInt(1, Integer.valueOf(homeworkId));
			resultSet=pstm.executeQuery();
			while(resultSet.next()){
				pictureAddr.add(saeSto.getUrl("pictures", resultSet.getString("addr")));
//				pictureAddr.add(pictureAction.getServlet().getServletContext().getContextPath()+"\\picutres\\"+resultSet.getString("addr"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				resultSet.close();
				pstm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			close();
		}
		return pictureAddr;
	}
	
	public void addNewHomework(HomeworkItem homeworkItem){
		
	}

	public long add2Homework(Integer courseId, String ps) throws Exception{
		// TODO Auto-generated method stub
		conn=getConnection();
		
		ResultSet resultSet=null;
		long lastId=0;
		try{
			pstm=conn.prepareStatement("insert into homework values(null,?,now(),?)");
			pstm.setInt(1, courseId);
			pstm.setString(2, ps);
			pstm.execute();
			resultSet=pstm.executeQuery("select LAST_INSERT_ID()");
			if(resultSet.next()){
				lastId=resultSet.getLong(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				resultSet.close();
				pstm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			close();
		}
		return lastId;
	}

	public void add2Picture(Integer courseId, String newFileName) throws Exception{
		// TODO Auto-generated method stub
		conn=getConnection();
		try{
			pstm=conn.prepareStatement("insert into picture values(null,?,?)");
			pstm.setInt(1, courseId);
			pstm.setString(2, newFileName);
			pstm.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				
				pstm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			close();
		}
	}

	public void deleteByHomeworkId(String homeworkId, DeleteAction deleteAction) throws Exception{
		SaeStorage ss=new SaeStorage();
		conn=getConnection();
		List<String> addrs=new ArrayList<String>();
		ResultSet resultSet=null;
		try{
			pstm=conn.prepareStatement("delete from homework where homework_id=?");
			pstm.setInt(1, Integer.valueOf(homeworkId));
			pstm.execute();
			pstm=conn.prepareStatement("select * from picture where homework_id=?");
			pstm.setInt(1, Integer.valueOf(homeworkId));
			resultSet=pstm.executeQuery();
			while(resultSet.next()){
				addrs.add(resultSet.getString("addr"));
			}
			for(String addr:addrs){
				ss.delete("pictures", addr);
			}
			pstm=conn.prepareStatement("delete from picture where homework_id=?");
			pstm.setInt(1, Integer.valueOf(homeworkId));
			pstm.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				
				pstm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			close();
		}
	}

	public int getTotalNumberOfHomework(Course course)throws Exception {
		conn=getConnection();
		ResultSet resultSet=null;
		try{
			pstm=conn.prepareStatement("select count(*) from homework where course_id=?");
			pstm.setInt(1, course.getCourseId());
			resultSet=pstm.executeQuery();
			while(resultSet.next()){
				return resultSet.getInt("count(*)");
			}
			return 0;
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}finally{
			try {
				resultSet.close();
				pstm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			close();
		}
		
	}
	
}
